package jsyf.oa.dao.report;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * Created by Administrator on 2018/2/7 0007.
 */
@Repository
public class RegionReportDao extends BaseDao {
    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }

    public List<JSONObject> getCompanyByManage(String dateTime) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  \n ");
        sb.append(" v_sys_company.companyId, \n");
        sb.append(" v_sys_company.companyName,  \n ");
        sb.append(" IFNULL(v_sys_company.managerName,'') as managerName,  \n ");
        sb.append(" IFNULL(v_sys_company.salerManagerName,'') as salerManagerName,  \n ");
        sb.append(" IFNULL(t_sys_regionNum.regionNum,0) as regionManagerNum,   \n ");
        sb.append(" IFNULL(t_sys_complete.complete,0) as complete,   \n ");
        sb.append(" (select SUM(roleName = '大区经理') FROM v_sys_user WHERE v_sys_user.companyId =   \n ");
        sb.append(" v_sys_company.companyId AND v_sys_user.delFlag=0) as bigManagerNum,  \n ");
        sb.append(" IFNULL((select COUNT(1) FROM t_sys_depart WHERE `NAME` like'%大区%' AND DEL_FLAG=0 AND t_sys_depart.COMPANY_ID=v_sys_company.companyId),0) as managerTargetNum\n ");
        sb.append(" FROM v_sys_company \n ");
        sb.append(" LEFT JOIN (  \n ");
        sb.append(" SELECT ENTOURAGE,V_SYS_USER.COMPANYID AS COMPANY_ID,COMPANYNAME,COUNT(DISTINCT ENTOURAGE) AS REGIONNUM \n");
        sb.append(" FROM  V_WRT_CUSTOMER_VISIT \n");
        sb.append(" LEFT JOIN V_SYS_USER ON V_WRT_CUSTOMER_VISIT.ENTOURAGEID = V_SYS_USER.ID \n");
        sb.append("WHERE \n");
        if (len == 10) {
            sb.append(" reportDate = " + "'" + dateTime + "' \n");
        } else {
            sb.append(" LEFT(reportDate,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND v_sys_user.roleName='区域经理' \n ");
        sb.append("  GROUP BY COMPANYID\n");
        sb.append("  ) t_sys_regionNum ON v_sys_company.companyId = t_sys_regionNum.COMPANY_ID  \n");
        sb.append(" LEFT JOIN (  \n ");
        sb.append(" SELECT COMPANY_ID,SUM(VISIT_MODE = 1) as complete from t_wrt_customer_visit \n ");
        sb.append(" LEFT JOIN v_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=v_sys_user.id \n ");
        sb.append(" where \n ");
        if (len == 10) {
            sb.append(" REPORT_DATE = " + "'" + dateTime + "' \n");
        } else {
            sb.append(" LEFT(REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND v_sys_user.roleName='大区经理' \n ");
        sb.append("  GROUP BY COMPANY_ID \n");
        sb.append("  ) as t_sys_complete on v_sys_company.companyId = t_sys_complete.COMPANY_ID  \n");
        sb.append(" where companyParentId = '201712161613432EAF27DBCCB7D0E6CA' \n");
        sb.append(" and v_sys_company.companyName NOT like '%大客户%' and v_sys_company.companyDelFlag = 0\n");
        sb.append(" ORDER BY v_sys_company.companySortNo");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getRegionByManage(String dateTime, String companyId) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* , CASE  \n ");
        sb.append(" WHEN @rowtotal = obj.orderNo THEN  \n");
        sb.append(" @rownum  \n ");
        sb.append(" WHEN @rowtotal := obj.orderNo THEN  \n ");
        sb.append(" @rownum :=@rownum + 1 \n ");
        sb.append(" WHEN @rowtotal = 0 THEN   \n ");
        sb.append(" @rownum :=@rownum + 1  \n ");
        sb.append(" END AS rownum\n ");
        sb.append(" FROM(\n ");
        sb.append(" SELECT\n ");
        sb.append(" t_sys_user.`id` AS managerId,\n ");
        sb.append(" t_sys_user.`name` AS managerName,\n ");
        sb.append(" manager.`JOB_STATE`  AS jobState,\n ");
        sb.append(" visit.ENTOURAGE_ID AS entourageId, \n ");
        sb.append(" visit.ENTOURAGE AS entourageName, \n ");
        sb.append(" t_sys_region.`NAME` AS regionName,\n ");
        sb.append(" REPLACE(t_sys_depart.`NAME`," + "'" + "大区" + "'," + "''" + ") AS departName, \n");
        sb.append(" COUNT(1) AS visitCount,\n ");
        sb.append(" IFNULL(t2.orderNo,0) as orderNo\n ");
        sb.append(" FROM `t_wrt_customer_visit` AS visit\n ");
        sb.append(" LEFT JOIN t_sys_user ON t_sys_user.id=visit.CREATE_USER_ID  \n ");
        sb.append(" LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID \n ");
        sb.append(" LEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n ");
        sb.append(" LEFT JOIN t_sys_depart on t_sys_role.DEPART_ID = t_sys_depart.ID \n ");
        sb.append(" LEFT JOIN t_sys_user AS manager ON manager.ID=visit.ENTOURAGE_ID \n ");
        sb.append(" LEFT JOIN t_sys_region  ON visit.ENTOURAGE_REGION_ID=t_sys_region.ID AND t_sys_region.COMPANY_ID=visit.COMPANY_ID \n ");
        sb.append(" LEFT JOIN ( \n ");
        sb.append(" SELECT t.CREATE_USER_ID,COUNT(1) as orderNo from (SELECT CREATE_USER_ID,ENTOURAGE_ID FROM t_wrt_customer_visit  \n ");
        sb.append(" WHERE  \n ");
        if (len == 10) {
            sb.append(" t_wrt_customer_visit.REPORT_DATE= " + "'" + dateTime + "' \n");
        } else {
            sb.append(" LEFT(t_wrt_customer_visit.REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND t_wrt_customer_visit.COMPANY_ID=" + "'" + companyId + "' \n ");
        sb.append(" AND t_wrt_customer_visit.ENTOURAGE_ID <> ''\n");
        sb.append(" GROUP BY CREATE_USER_ID,ENTOURAGE_ID) as t GROUP BY t.CREATE_USER_ID ) as t2 on visit.CREATE_USER_ID = t2.CREATE_USER_ID  \n");
        sb.append(" WHERE\n");
        if (len == 10) {
            sb.append(" visit.REPORT_DATE= " + "'" + dateTime + "' \n");
        } else {
            sb.append(" LEFT(visit.REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND t_sys_role_name.`NAME`='大区经理' \n");
        sb.append(" AND visit.VISIT_MODE=1 \n");
        sb.append(" AND visit.COMPANY_ID=" + "'" + companyId + "'\n");
        sb.append(" GROUP BY visit.CREATE_USER_ID,visit.ENTOURAGE,visit.ENTOURAGE_ID,t_sys_user.`NAME` \n");
        sb.append(" ORDER BY orderNo  DESC,t_sys_user.`name` ) as obj,\n");
        sb.append(" (SELECT @rownum := 0 ,@rowtotal := NULL) r");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getCustomerByManage(String dateTime, String userId, String entourage) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  \n ");
        sb.append(" t_sys_user.`name` AS managerName, \n");
        sb.append(" manager.`JOB_STATE`  AS jobState, \n ");
        sb.append(" manager.ENTRY_DATE  AS entryDate,   \n ");
        sb.append(" visit.ENTOURAGE AS entourageName,  \n ");
        sb.append(" customer.TEL AS customerTel,  \n ");
        sb.append(" visit.ID AS customerId,  \n ");
        sb.append(" visit.ARRIVALS_TIME AS arrivalsTime,\n");
        sb.append(" visit.LEAVE_TIME AS leaveTime, \n");
        sb.append(" customer.`NAME` AS customerName,  \n ");
        sb.append(" customer.WECHAT AS customerWechat,  \n ");
        sb.append(" IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(visit.LEAVE_TIME)-UNIX_TIMESTAMP(visit.ARRIVALS_TIME))/60),0) as time\n ");
        sb.append(" FROM `t_wrt_customer_visit` AS visit  \n ");
        sb.append(" LEFT JOIN t_sys_user ON t_sys_user.id=visit.CREATE_USER_ID\n ");
        sb.append(" LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID  \n ");
        sb.append(" LEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n ");
        sb.append(" LEFT JOIN t_sys_depart on t_sys_role.DEPART_ID = t_sys_depart.ID \n ");
        sb.append(" LEFT JOIN t_sys_user AS manager ON manager.ID=visit.ENTOURAGE_ID\n ");
        sb.append(" LEFT JOIN t_wrt_customer AS customer ON visit.CUSTOMER_ID=customer.ID\n ");
        sb.append(" where\n ");
        if (len == 10) {
            sb.append(" visit.REPORT_DATE = " + "'" + dateTime + "' \n");
        } else {
            sb.append(" LEFT(visit.REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND t_sys_role_name.`NAME`='大区经理' \n ");
        sb.append(" AND visit.VISIT_MODE=1 \n ");
        sb.append(" AND visit.CREATE_USER_ID=" + "'" + userId + "'");
        sb.append(" AND visit.ENTOURAGE=" + "'" + entourage + "'\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public JSONObject getByAllCompanyId(String dateTime) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n");
        sb.append(" IFNULL(SUM(ENTOURAGE='无'),0) as noAccompany,\n");
        sb.append(" IFNULL(SUM(ENTOURAGE!='无'),0) as accompany \n ");
        sb.append(" from t_wrt_customer_visit \n");
        sb.append(" LEFT JOIN v_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=v_sys_user.id \n");
        sb.append(" where\n");
        sb.append(" COMPANY_ID IN (SELECT ID from t_sys_company WHERE PARENT_ID='201712161613432EAF27DBCCB7D0E6CA') \n");
        if (len == 10) {
            sb.append("AND REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("AND LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" and v_sys_user.roleName='大区经理'\n");
        sb.append(" AND VISIT_MODE=1\n");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public JSONObject getByCompanyId(String companyId, String dateTime, String yesterday) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n");
        sb.append(" IFNULL(SUM(ENTOURAGE='无'),0)as noAccompany,\n");
        sb.append(" IFNULL(SUM(ENTOURAGE!='无'),0)as accompany \n ");
        sb.append(" from t_wrt_customer_visit\n");
        sb.append(" LEFT JOIN v_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=v_sys_user.id\n");
        sb.append("  where \n");
        sb.append("  COMPANY_ID=" + "'" + companyId + "' \n");
        if (len == 10) {
            sb.append(" AND REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append(" AND LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" and v_sys_user.roleName='大区经理'\n");
        sb.append(" AND VISIT_MODE=1\n");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public JSONObject getByUserId(String userId, String dateTime, String yesterday) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n");
        sb.append(" IFNULL(SUM(ENTOURAGE='无'),0)as noAccompany,\n");
        sb.append(" IFNULL(SUM(ENTOURAGE!='无'),0)as accompany\n ");
        sb.append(" from t_wrt_customer_visit where\n");
        if (len == 10) {
            sb.append("REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" AND CREATE_USER_ID=" + "'" + userId + "'\n");

        sb.append(" AND VISIT_MODE=1 \n");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public Date nowDate() {
        return this.getJdbcTemplate().queryForObject("select curdate();", Date.class);
    }
}
